#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import datetime
import os
import sys

from aliyun.log import LogClient, GetLogsRequest

from tools import game, game_port
from tools import checkFile, get_specified_timestamps, format_timestamp
import pandas as pd
import mysql.connector
from enum import Enum
import json


class TexasHoldemStats:
    def __init__(self, uid, bet_num, prize, income):
        self.uid = uid
        self.bet_num = bet_num
        self.prize = prize
        self.income = income

    def __str__(self):
        return f"用户ID: {self.uid}, 下注总额: {self.bet_num}, 奖励总额: {self.prize}, 总收益: {self.income}"


# 定义德州扑克游戏记录对象
class TexasHoldemRecord:
    def __init__(self, game_id, bet_place_id, bet_num, prize, reward_result, reward_type):
        self.game_id = game_id
        self.bet_place_id = bet_place_id
        self.bet_num = bet_num
        self.prize = prize
        self.reward_result = reward_result
        self.reward_type = reward_type

    def __str__(self):
        return f"游戏ID: {self.game_id}, 下注位置: {get_bet_place_name(self.bet_place_id)}, " \
               f"下注金额: {self.bet_num}, 奖励: {self.prize}, " \
               f"开奖结果: {self.reward_result}, 开奖类型: {get_reward_type_name(self.reward_type)}"


# 获取下注位置名称的函数
def get_bet_place_name(bet_place_id):
    """
    根据下注位置ID获取对应的名称

    参数:
        bet_place_id: 下注位置ID

    返回:
        下注位置名称
    """
    bet_place_names = {
        BetPlaceId.A_WIN.value: "A赢",
        BetPlaceId.B_WIN.value: "B赢",
        BetPlaceId.DRAW.value: "平局",
        BetPlaceId.FLUSH_STRAIGHT_STRAIGHT_FLUSH.value: "同花/连牌/同花连牌",
        BetPlaceId.PAIR.value: "对子",
        BetPlaceId.PAIR_A.value: "对A",
        BetPlaceId.HIGH_CARD_ONE_PAIR.value: "高牌/一对",
        BetPlaceId.TWO_PAIR.value: "两对",
        BetPlaceId.THREE_KIND_STRAIGHT_FLUSH.value: "三条/顺子/同花",
        BetPlaceId.FULL_HOUSE.value: "葫芦",
        BetPlaceId.FOUR_KIND_STRAIGHT_FLUSH_ROYAL_FLUSH.value: "金刚/同花顺/皇同"
    }
    return bet_place_names.get(bet_place_id, "未知")


# 获取奖励类型名称的函数
def get_reward_type_name(reward_type):
    """
    根据奖励类型ID获取对应的名称

    参数:
        reward_type: 奖励类型ID

    返回:
        奖励类型名称
    """
    reward_type_names = {
        RewardType.MODEL_REWARD.value: "大模型开奖",
        RewardType.FALLBACK_INSUFFICIENT_FUNDS.value: "兜底开奖(模型开奖赔付不起)",
        RewardType.FALLBACK_MODEL_ERROR.value: "兜底开奖(模型开奖报错)",
        RewardType.FALLBACK_MODEL_TIMEOUT.value: "兜底开奖(模型超时)",
        RewardType.PARSE_MODEL_RESULT_FAILED.value: "无法解析模型结果",
        RewardType.MODEL_DATA_ERROR.value: "模型数据错误",
        RewardType.OTHER.value: "其他",
        RewardType.NOT_REWARDED_COMPENSATION.value: "未开奖，补偿任务"
    }
    return reward_type_names.get(reward_type, "未知")


# 定义德州扑克下注位置枚举
class BetPlaceId(Enum):
    A_WIN = 1  # A赢
    B_WIN = 2  # B赢
    DRAW = 3  # 平局
    FLUSH_STRAIGHT_STRAIGHT_FLUSH = 4  # 同花/连牌/同花连牌
    PAIR = 5  # 对子
    PAIR_A = 6  # 对A
    HIGH_CARD_ONE_PAIR = 7  # 高牌/一对
    TWO_PAIR = 8  # 两对
    THREE_KIND_STRAIGHT_FLUSH = 9  # 三条/顺子/同花
    FULL_HOUSE = 10  # 葫芦
    FOUR_KIND_STRAIGHT_FLUSH_ROYAL_FLUSH = 11  # 金刚/同花顺/皇同


# 定义奖励类型枚举
class RewardType(Enum):
    MODEL_REWARD = 1  # 大模型开奖
    FALLBACK_INSUFFICIENT_FUNDS = 2  # 兜底开奖(模型开奖赔付不起)
    FALLBACK_MODEL_ERROR = 3  # 兜底开奖(模型开奖报错)
    FALLBACK_MODEL_TIMEOUT = 4  # 兜底开奖(模型超时)
    PARSE_MODEL_RESULT_FAILED = 5  # 无法解析模型结果
    MODEL_DATA_ERROR = 6  # 模型数据错误
    OTHER = 11  # 其他
    NOT_REWARDED_COMPENSATION = 21  # 未开奖，补偿任务


def get_texas_holdem_stats(start_time, end_time):
    """
    查询德州扑克游戏统计数据并返回结果

    参数:
        start_time: 开始时间戳
        end_time: 结束时间戳

    返回:
        查询结果的DataFrame
    """
    connection = None
    cursor = None
    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(
            host=game,
            user="azal_game_ro",
            password="76HpdqKVsr1LkIdc",
            database="game",
            port=game_port
        )

        # 创建一个游标对象
        cursor = connection.cursor()

        # 执行查询
        query = """
        SELECT
            uid,
            SUM( bet_num ) AS betNum,
            SUM(  COALESCE( prize, 0 )  ) AS prize,
            SUM(  COALESCE( prize, 0 )  ) - SUM( bet_num ) AS income
        FROM
            uc_game_texas_holdem_user_record
        WHERE
            create_time BETWEEN %s AND %s
        AND uid NOT IN (
                1000014,1000015,1000079,1000079,1000251,1008465,10941724,10965098,10976377,10976377,11078796,11078798,
                11550144,11566429,11704405,11713484,11774473,11786352,11786352,11793957,11917833,11918721,11994647,
                12096378,12116191,1234421,12407745,12595859,13420655,13421443,13772075,13786205,13786540,13786725,
                13786725,13801020,1382857,14226097,14227714,14243022,14243071,14243394,1445425,14731824,14732048,
                15165726,15166199,15166856,15169124,15169124,15195000,15196873,1561552,1588335,15906077,15906129,
                1609467,16233296,16233296,16309255,16309396,1633044,1633365,1660885,1663629,1663647,1663661,1682181,
                1696773,1720199,1755189,1755435,1755562,1756499,1756677,1756677,1774008,1780606,1862850,1862919,
                1896678,1903532,1903532,1908693,1908693,1909532,1909532,1916847,1947218,1979612,2024786,2129592,
                2195628,2212390,2577077,2581916,2635462,2668982,2669147,2669161,2677006,2678608,2678709,2678715,
                2678915,2678932,2678949,2678971,2679102,2679131,2679222,2679311,2679321,2680733,2680753,2680784,
                2680796,2680975,2680975,2683081,2683140,2690531,2690539,2690549,2705794,2716029,2734003,2738435,
                2738435,2842850,2842884,2842902,2843210,2913068,2933956,3141207,3141207,3169896,3453279,3562877,
                3991682,3999691,3999691,3999691,4097606,4420576,4420576,4565754,4567544,4577977,4578026,4578121,
                4578149,4606913,4607469,4700578,4725624,4725713,4725851,4727061,4727482,4727517,4727545,4727573,
                4727594,4727813,4800863,625527498,6760013,6789785,7100114,7100114,7884742,7884742,7937621,7937621,
                7937631,7937647,7937647,7937651,7937662,7937672,7937688,7937699,7937699,7937712,7937712,7937712,
                7937714,7937714,7937717,7937728,7937728,7937740,7937740,7937740,7937755,7937758,7937774,7937806,
                7937811,7937820,8062089,8083893,8095978,8316140,8392630,8392630,8394479,8479254,8479254,8752481,
                8828395,8862241,8982410,9390737,9628342,6971547,2854683,15196873
            )
        GROUP BY
            uid
        ORDER BY
            income DESC
        """
        cursor.execute(query, (start_time, end_time))
        results = cursor.fetchall()

        result_list = [TexasHoldemStats(*row) for row in results]
        return result_list

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        return []

    finally:
        # 确保游标和连接被关闭
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def write_user_accepted_info_to_excel_pandas(user_accepted_infos, file_name="user_data.xlsx"):
    # 将UserAcceptedInfo对象列表转换为字典列表
    data = []
    for user_info in user_accepted_infos:
        data.append({
            "用户ID": user_info.uid,
            "下注钻石": int(user_info.bet_num),
            "赢取钻石": int(user_info.prize),
            "收入(赢取钻石-下注钻石)": int(user_info.income)
        })

    # 创建DataFrame
    df = pd.DataFrame(data)

    # 使用ExcelWriter可以设置更多格式选项
    with pd.ExcelWriter(file_name, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='用户数据', index=False)

        # 获取worksheet对象以进行格式调整
        worksheet = writer.sheets['用户数据']

        # 自动调整列宽
        for i, col in enumerate(df.columns):
            column_width = max(df[col].astype(str).map(len).max() * 2, (len(col) + 2) * 2)
            worksheet.column_dimensions[worksheet.cell(row=1, column=i + 1).column_letter].width = column_width

    print(f"数据已成功写入 {file_name}")


def download_aliyun_log(start_time, end_time):
    end_time = end_time + 200
    # 初始化客户端
    accessKeyId = "LTAI4FssSMQ2cDnehiH8JHin"
    accessKey = "tX06dGLhTtbYHR3TK9CPkZ0Qe0X5Wc"
    endpoint = "me-east-1.log.aliyuncs.com"  # 替换为您的区域Endpoint
    client = LogClient(endpoint, accessKeyId, accessKey)

    # 定义Project和Logstore名称
    project_name = "xys-abroad-system"
    logstore_name = "common-log"
    query = "request_path: TexasHoldemCommonService.postForString"

    offset = 0
    size = 100  # 每次获取的日志条数

    result = {}

    while True:
        request = GetLogsRequest(project_name, logstore_name, start_time, end_time, query=query, offset=offset,
                                 line=size)
        response = client.get_logs(request)
        # response = client.get_log(project_name, logstore_name, start_time, end_time, query=query, offset=offset,
        #                           size=size)
        logs_data = response.get_logs()

        if not logs_data:
            print(f"log为null")
            break

        for log in logs_data:

            for k, v in log.contents.items():
                if k == "request_body":
                    request_body_array = json.loads(v)
                    game_id = request_body_array[1]
                    prompt = request_body_array[3]
                    result[game_id] = prompt

        offset += size

    print("日志处理完毕")
    return result


def fetch_user_accepted_detail_info_with_time(uid, start_time, end_time):
    connection = None
    cursor = None
    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(
            host=game,
            user="azal_game_ro",
            password="76HpdqKVsr1LkIdc",
            database="game",
            port=game_port
        )

        # 创建一个游标对象
        cursor = connection.cursor()
        # 执行查询
        query = """
                SELECT
                    t1.game_id,
                    t1.bet_place_id,
                    t1.bet_num,
                    t1.prize,
                    t2.reward_result,
                    t2.reward_type
                FROM uc_game_texas_holdem_user_record t1
                LEFT JOIN uc_game_texas_holdem_reward_record t2 ON  t1.game_id=t2.id
                WHERE t1.uid=%s AND t1.create_time BETWEEN %s AND %s
                order by t1.game_id asc
            """
        cursor.execute(query, (uid, start_time, end_time))

        # 获取查询结果并封装成对象放入列表中
        results = cursor.fetchall()
        result_list = [TexasHoldemRecord(*row) for row in results]

        # 按照gameId分组结果
        grouped_records = {}
        for record in result_list:
            if record.game_id not in grouped_records:
                grouped_records[record.game_id] = []
            grouped_records[record.game_id].append(record)
        return grouped_records

    except mysql.connector.Error as err:
        print(f"Error: {err}")
        return {}

    finally:
        # 确保游标和连接被关闭
        if cursor:
            cursor.close()
        if connection:
            connection.close()


def get_reward_result_names(reward_result):
    """
    将reward_result数组转换为对应的下注位置名称列表

    参数:
        reward_result: 奖励结果数组字符串，如"[1,4,5,10]"

    返回:
        下注位置名称列表的字符串
    """
    if not reward_result:
        return "未知"

    try:
        # 移除方括号并分割字符串
        result_list = reward_result.strip('[]').split(',')
        # 将字符串转换为整数并获取对应的名称
        names = [get_bet_place_name(int(x.strip())) for x in result_list]
        return f"[{', '.join(names)}]"
    except:
        return "未知"


def export_user_records_to_excel(grouped_records, prompt_dict, excel_filename):
    """
    将用户游戏记录导出到Excel文件

    参数:
        grouped_records: 按游戏ID分组的记录
        uid: 用户ID
        start_time: 开始时间戳
        end_time: 结束时间戳

    返回:
        生成的Excel文件名
    """
    # 创建数据列表用于转换为DataFrame
    data = []

    # 记录每个游戏ID的起始行和结束行，用于后续合并单元格
    merge_ranges = {}
    current_row = 0

    # 遍历分组记录，按照图片中的格式整理数据
    for game_id, records in grouped_records.items():
        start_row = current_row

        # 获取该游戏ID下的公共信息（所有记录的reward信息应该相同）
        reward_result = records[0].reward_result
        # 获取reward_result对应的名称
        reward_result_names = get_reward_result_names(reward_result)
        reward_type = records[0].reward_type

        # 计算该组的bet_num总和
        total_bet_num = sum(record.bet_num for record in records)
        # 计算该组的prize总和
        total_prize = sum(record.prize if record.prize is not None else 0 for record in records)
        # 计算该组的总收益
        total_income = total_prize - total_bet_num

        # 添加每条记录
        for record in records:
            row_data = {
                "gameId": game_id,
                "betPlaceId": record.bet_place_id,
                "betPlaceName": get_bet_place_name(record.bet_place_id),
                "betNum": int(record.bet_num),
                "rewardResult": reward_result,
                "rewardResultNames": reward_result_names,
                "rewardType": reward_type,
                "rewardTypeName": get_reward_type_name(reward_type) if reward_type else "未知",
                "prompt": prompt_dict.get(game_id, "未知"),  # 使用prompt_dict获取prompt，如果不存在则使用"未知"或其他默认值
                "betTotal": int(total_bet_num),  # 使用计算出的总和
                "prize": int(total_prize),  # 使用组的总奖励
                "income": int(total_income)  # 使用组的总收益
            }
            data.append(row_data)
            current_row += 1

        # 记录合并范围
        if current_row > start_row + 1:  # 如果有多条记录
            merge_ranges[game_id] = (start_row, current_row - 1)

    # 创建DataFrame
    df = pd.DataFrame(data)

    # 使用ExcelWriter以便能够合并单元格
    with pd.ExcelWriter(excel_filename, engine='openpyxl') as writer:
        df.to_excel(writer, index=False, sheet_name='用户游戏记录')

        # 获取工作表
        worksheet = writer.sheets['用户游戏记录']

        # 设置列宽
        column_widths = {
            'A': 7,  # gameId
            'B': 10,  # bet_place_id
            'C': 15,  # bet_place_name
            'D': 10,  # bet_num
            'E': 15,  # reward_result
            'F': 30,  # reward_result_names
            'G': 10,  # reward_type
            'H': 25,  # rewardTypeName
            'I': 50,  # betTotal
            'J': 20,  # prize
            'K': 20,  # income
            'L': 20  # income
        }

        # 应用列宽设置
        for col, width in column_widths.items():
            worksheet.column_dimensions[col].width = width

        # 合并单元格
        for game_id, (start_row, end_row) in merge_ranges.items():
            # Excel行从1开始，且有标题行，所以需要+2
            # 合并gameId列 (A列)
            worksheet.merge_cells(f'A{start_row + 2}:A{end_row + 2}')

            # 合并rewardAnimalId列 (E列)
            worksheet.merge_cells(f'E{start_row + 2}:E{end_row + 2}')

            # 合并rewardAnimalName列 (F列)
            worksheet.merge_cells(f'F{start_row + 2}:F{end_row + 2}')

            # 合并CV列 (G列)
            worksheet.merge_cells(f'G{start_row + 2}:G{end_row + 2}')

            # 合并rewardTypeName列 (H列)
            worksheet.merge_cells(f'H{start_row + 2}:H{end_row + 2}')

            # 合并betTotal列 (I列)
            worksheet.merge_cells(f'I{start_row + 2}:I{end_row + 2}')

            # 合并prize列 (J列)
            worksheet.merge_cells(f'J{start_row + 2}:J{end_row + 2}')

            # 合并income列 (K列)
            worksheet.merge_cells(f'K{start_row + 2}:K{end_row + 2}')

            # 合并income列 (L列)
            worksheet.merge_cells(f'L{start_row + 2}:L{end_row + 2}')

    print(f'用户游戏记录已成功写入到文件: {excel_filename}')
    return excel_filename


def texas_holdem_poker_analysis():
    start_time, end_time = get_specified_timestamps()

    parent_dir_path = datetime.datetime.fromtimestamp(start_time).strftime("%Y-%m-%d")
    if not os.path.exists(parent_dir_path):
        # 创建新的文件夹
        try:
            os.mkdir(parent_dir_path)
            print(f"成功创建文件夹: {parent_dir_path}")
        except Exception as e:
            print(f"创建文件夹时出错: {e}")
            sys.exit(1)

    folder_name = "德州"

    checkFile(os.path.join(parent_dir_path, folder_name))

    start_time, end_time = get_specified_timestamps()

    # 德州扑克每日所有用户的下注信息
    user_accepted_infos = get_texas_holdem_stats(start_time, end_time)
    # 判断结果是否为空
    if not user_accepted_infos:
        print(
            f"该时间段内无数据,跳过处理: {format_timestamp(start_time)} 到 {format_timestamp(end_time)}")
        return

    daily_user_data_file = os.path.join(parent_dir_path, folder_name,
                                        f"德州北京时间{format_timestamp(start_time)}到{format_timestamp(end_time)}所有用户下注收益信息统计.xlsx")
    write_user_accepted_info_to_excel_pandas(user_accepted_infos, daily_user_data_file)

    # 处理阿里云日志
    prompt_dict = download_aliyun_log(start_time, end_time)

    # 德州每日赢钻石最多的用户的详细下注信息
    first_user_accepted_info = user_accepted_infos[0]
    user_id = first_user_accepted_info.uid

    process_user_data_file = os.path.join(parent_dir_path, folder_name,
                                          f"德州北京时间{format_timestamp(start_time)}到{format_timestamp(end_time)}用户{user_id}下注收益信息_已处理.xlsx")

    grouped_records = fetch_user_accepted_detail_info_with_time(user_id, start_time, end_time)
    export_user_records_to_excel(grouped_records, prompt_dict, process_user_data_file)


if __name__ == "__main__":
    texas_holdem_poker_analysis()
